package com.limao.Admin.DBProvider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.limao.Admin.DataClass.AdminSysMessClass;
import com.limao.DbConnection.DbConnectionManager;
import com.limao.Tool.CacheClass;

public class AdminSysMessProvider {

	private static class AdminSysMessProviderContainer {
		private static AdminSysMessProvider instance = new AdminSysMessProvider();
	}

	public static AdminSysMessProvider getInstance() {
		return AdminSysMessProviderContainer.instance;
	}
	
	// 初始化
	private AdminSysMessProvider() {}

	public  List<Object> Admin_SysMessListInfo(long startTime, long endTime,int state,int type,int objs, int curPage)
	{
		ArrayList<Object> list = new ArrayList<Object>();
		List<AdminSysMessClass> userList = new ArrayList<AdminSysMessClass>();
		int recordCount = 0;
		int pageSize = 20;
		int totalPage = 0;

		Connection con = null;
		PreparedStatement cStmt = null;
		ResultSet rs = null;

		try {
			con = DbConnectionManager.getConnection();
			cStmt = con.prepareCall("call Manager_searchSysMessDate(?,?,?,?,?,?)");
			cStmt.setLong(1, startTime);
			cStmt.setLong(2, endTime);
			cStmt.setInt(3, state);
			cStmt.setInt(4, type);
			cStmt.setInt(5, objs);
			cStmt.setInt(6, curPage);

			
			boolean hadResults = cStmt.execute();
			int i = 0;
			while (hadResults) {
				rs = cStmt.getResultSet();
				if (i == 0) {
					while (rs.next()) {
						AdminSysMessClass tmp=new AdminSysMessClass();
						tmp.id= rs.getInt("id") + ""; 
					    tmp.type= rs.getInt("type") + "";	
					    tmp.title= rs.getString("title");	
					    tmp.txt= rs.getString("txt");
					    tmp.image= rs.getString("image");
					    tmp.androidurl= rs.getString("androidurl");
					    tmp.iosurl= rs.getString("iosurl");
					    
					    tmp.objs= rs.getInt("objs") + "";
					    tmp.term= rs.getInt("term") + "";
					    tmp.username= rs.getString("username");
					    tmp.value1= rs.getFloat("value1") + "";
					    tmp.value2= rs.getFloat("value2") + "";
					    tmp.state= rs.getInt("state")+"";
					    tmp.createtime= rs.getLong("createtime") + "";
					    tmp.SetCityname(rs.getFloat("value1"));
					    tmp.SetShengname(rs.getFloat("value2"));

				    	userList.add(tmp);
					}
				} else {
					if (rs.next()) {
						recordCount = rs.getInt(1);
						totalPage = (recordCount + pageSize - 1) / pageSize;
					}
				}
				hadResults = cStmt.getMoreResults();
				i++;
			}

		} catch (Exception e) {
			e.printStackTrace();
			userList=null;
		} finally {
			DbConnectionManager.closeConnection(rs, cStmt, con);
		}
		list.add(userList);
		list.add(recordCount);
		list.add(totalPage);
		
		return list;		
	}
	
	
	//增加系统消息
	public AdminSysMessClass AddSysMess(int type,String title,String txt,String image,String androidurl,String iosurl,
			int objs,int term,String username,float value1,float value2)
	{
		AdminSysMessClass tmp=null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			long time = new Date().getTime();
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("INSERT INTO cd_sys_message " +
					"(type,title,txt,image,androidurl,iosurl,objs,term,username,value1,value2,state,createtime) " +
					"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ");

			pstmt.setInt(1, type);
			pstmt.setString(2, title);
			
			if (!CacheClass.isEmpty(txt)) {
				pstmt.setString(3, txt);
			} else {
				pstmt.setNull(3, Types.VARCHAR);
			}

			if (!CacheClass.isEmpty(image)) {
				pstmt.setString(4, image);
			} else {
				pstmt.setNull(4, Types.VARCHAR);
			}
			
			if (!CacheClass.isEmpty(androidurl)) {
				pstmt.setString(5, androidurl);
			} else {
				pstmt.setNull(5, Types.VARCHAR);
			}
			
			if (!CacheClass.isEmpty(iosurl)) {
				pstmt.setString(6, iosurl);
			} else {
				pstmt.setNull(6, Types.VARCHAR);
			}
			
			pstmt.setInt(7, objs);
			pstmt.setInt(8, term);
			
			if (!CacheClass.isEmpty(username)) {
				pstmt.setString(9, username);
			} else {
				pstmt.setNull(9, Types.VARCHAR);
			}			
			
			pstmt.setFloat(10, value1);
			pstmt.setFloat(11, value2);
			pstmt.setInt(12, 0);
			pstmt.setLong(13, time);
			pstmt.execute();
			
			//取这个消息的id
			DbConnectionManager.fastcloseStmt(rs,pstmt);
			pstmt = con.prepareStatement("select * from cd_sys_message where createtime=? ");
			pstmt.setLong(1, time);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				tmp=new AdminSysMessClass();
				tmp.id= rs.getInt("id") + ""; 
			    tmp.type= rs.getInt("type") + "";	
			    tmp.title= rs.getString("title");	
			    tmp.txt= rs.getString("txt");
			    tmp.image= rs.getString("image");
			    tmp.androidurl= rs.getString("androidurl");
			    tmp.iosurl= rs.getString("iosurl");
			    tmp.objs= rs.getInt("objs") + "";
			    tmp.term= rs.getInt("term") + "";
			    tmp.username= rs.getString("username");
			    tmp.value1= rs.getFloat("value1") + "";
			    tmp.value2= rs.getFloat("value2") + "";
			    tmp.state= rs.getInt("state")+"";
			    tmp.createtime= rs.getLong("createtime") + "";
			}
		} catch (Exception e) {
			tmp=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs,pstmt, con);
		}
		
		return tmp;
	}
	
	public AdminSysMessClass querySysMessById(int id)
	{
		AdminSysMessClass tmp=null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select * from cd_sys_message where id=? ");
			pstmt.setLong(1, id);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				tmp=new AdminSysMessClass();
				tmp.id= rs.getInt("id") + ""; 
			    tmp.type= rs.getInt("type") + "";	
			    tmp.title= rs.getString("title");	
			    tmp.txt= rs.getString("txt");
			    tmp.image= rs.getString("image");
			    tmp.androidurl= rs.getString("androidurl");
			    tmp.iosurl= rs.getString("iosurl");
			    tmp.objs= rs.getInt("objs") + "";
			    tmp.term= rs.getInt("term") + "";
			    tmp.username= rs.getString("username");
			    tmp.value1= rs.getFloat("value1") + "";
			    tmp.value2= rs.getFloat("value2") + "";
			    tmp.state= rs.getInt("state")+"";
			    tmp.createtime= rs.getLong("createtime") + "";
			}
		} catch (Exception e) {
			tmp=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs,pstmt, con);
		}
		
		return tmp;
	}
	
	public  void deleSysMessById(int id)
	{

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("DELETE from cd_sys_message where id=? ");
			pstmt.setInt(1, id);
			pstmt.execute();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	
	}
	public static void UpdateSysMessState(int id,int state) 
	{
		
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("update cd_sys_message set state=? where id=? ");
			pstmt.setInt(1, state);
			pstmt.setInt(2, id);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
		
	}
	

}
